USE [JaxDug]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT [name] FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.GetPresentationsByDate') AND TYPE IN (N'P', N'PC'))
BEGIN
	DROP PROC GetPresentationsByDate
	PRINT 'DROP PROC GetPresentationsByDate'
END
GO

-- ===============================================================
-- Author:		RbH
-- Create date: 2012.03.21
-- Description:	Selects basic info about Presentations by date
-- Ussage:		EXEC GetPresentationsByDate '2012-01-01'
-- ===============================================================
CREATE PROCEDURE dbo.GetPresentationsByDate
	@Date DATETIME2
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @startDate AS DATE
	DECLARE @endDate AS DATE
	
	SET	@startDate = DATEADD(dd, 0, DATEDIFF(dd, 0, @Date))
	SET	@endDate = DATEADD(dd, 1, DATEDIFF(dd, 0, @Date))

	DECLARE @PresentationIDs TABLE ( PresentationID INT )
	
	INSERT INTO @PresentationIDs ( PresentationID )
	SELECT p.PresentationID
	FROM dbo.Presentations p
	WHERE p.StartDateTime BETWEEN @startDate AND @endDate
		
    SELECT
		p.PresentationID, p.Title, p.StartDateTime, p.EndDateTime,
		u.UserID AS 'SpeakerID', u.FirstName, u.LastName
	FROM dbo.Presentations p
		INNER JOIN @PresentationIDs tv ON p.PresentationID = tv.PresentationID
		INNER JOIN dbo.Users u ON u.UserID = p.SpeakerID
	ORDER BY p.PresentationID ASC
	
	SELECT DISTINCT pt.PresentationID, t.TagID, t.Tag
	FROM dbo.Tags t
		INNER JOIN dbo.PresentationTags pt ON t.TagID = pt.TagID
		INNER JOIN @PresentationIDs tv ON pt.PresentationID = tv.PresentationID
END
GO

PRINT 'CREATE PROC GetPresentationsByDate'

GRANT EXEC ON GetPresentationsByDate TO PublicDbUser
GO

PRINT 'GRANT EXEC ON GetPresentationsByDate TO PublicDbUser'